set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=20;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=20; --  每天生成 60 个分区


insert overwrite table jms_dm.dm_rate_order_source_mapping partition(dt)
select island_code, island_name, date_time, data_type, dt
from (select *,
             row_number() over (partition by island_code, island_name,dt order by data_type) rn
      from (select island_code, island_name, date(dt) date_time, 1 data_type, dt
            from jms_dm.dm_agency_province_pre_reach_rate_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and island_code not like '-%'
              and island_name not like '-%'
            group by dt, island_code, island_name
            union all
            select island_code, island_name, date(dt) date_time, 2 data_type, dt
            from jms_dm.dm_agency_province_pre_reach_rate_plan_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and island_code not like '-%'
              and island_name not like '-%'
            group by dt, island_code, island_name
            union all
            select island_code, island_name, date(dt) date_time, 3 data_type, dt
            from jms_dm.dm_agency_province_pre_reach_rate_sign_dt
            where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
                and '{{ execution_date | cst_ds }}'
              and island_code not like '-%'
              and island_name not like '-%'
            group by dt, island_code, island_name) detail) etl_detail
where rn = 1 distribute by dt,1;
